﻿using NORM.Common;
using NORM.DataBase;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DevelopAssistant.Service
{
    public class CreateOrReplaceObject
    {
        protected static string getObject(object objectName, string ProviderName)
        {
            if (AppSettings.EditorSettings.AutoSupplementary)
            {
                switch (ProviderName)
                {
                    case "System.Data.SQL":
                    case "System.Data.Sql": objectName = "[" + objectName + "]"; break;
                    case "System.Data.Sqlite": objectName = "[" + objectName + "]"; break;
                    case "System.Data.OleDb": objectName = "[" + objectName + "]"; break;
                    case "System.Data.Postgresql":
                    case "System.Data.PostgreSql": objectName = "\"" + objectName + "\""; break;
                    case "System.Data.MySql": objectName = "`" + objectName + "`"; break;
                    case "System.Data.Oracle": objectName = "[" + objectName + "]"; break;
                    default: break;
                }
            }
            return "" + objectName + "";
        }

        private static string getSqlDataType(object typeName, string len)
        {
            string rvl = string.Empty;

            if (!string.IsNullOrEmpty(len))
            {
                if ((typeName + "").ToUpper().StartsWith("INT"))
                    rvl = typeName + "";
                else if ((typeName + "").ToUpper().StartsWith("TEXT"))
                    rvl = typeName + "";
                else if ((typeName + "").ToUpper().StartsWith("DATE"))
                    rvl = typeName + "";
                else if ((typeName + "").ToUpper().StartsWith("IMAGE"))
                    rvl = typeName + "";
                else if ((typeName + "").ToUpper().StartsWith("DATETIME"))
                    rvl = typeName + "";
                else
                    rvl = typeName + "(" + len + ")";
            }
            else
            {
                rvl = typeName + "";
            }

            return rvl;
        }

        protected static string getObjectType(object typeName, string len, string ProviderName)
        {
            switch (ProviderName)
            {
                case "System.Data.SQL":
                case "System.Data.Sql": typeName = getSqlDataType(typeName, len); break;
                case "System.Data.Sqlite": typeName = getSqlDataType(typeName, len); break;
                case "System.Data.OleDb": typeName = getSqlDataType(typeName, len); break;
                case "System.Data.Postgresql":
                case "System.Data.PostgreSql": typeName = getPostgreSqlType(typeName, len); break;
                case "System.Data.MySql": typeName = getSqlDataType(typeName, len); break;
                case "System.Data.Oracle": typeName = getSqlDataType(typeName, len); break;
                default: break;
            }
            return typeName + "";

        }

        private static string getPostgreSqlType(object typeName, string len)
        {
            string rvl = string.Empty;

            if (!string.IsNullOrEmpty(len))
            {
                if (len != "0")
                    rvl = typeName + "(" + len + ")";
                else
                    rvl = typeName + "";
            }
            else
            {
                rvl = typeName + "";
            }

            return rvl;
        }

        public static string ToSnippetCode(string ObjectName, DataBaseServer DatabaseServer)
        {
            StringPlus sp = new StringPlus();

            string SqlTextView = "";
            string ProcedureName = "sp_helptext";
            OsqlParameter[] dbParameters = null;
            DataTable dt = new DataTable();

            using (var db = Utility.GetAdohelper(DatabaseServer))
            {
                switch (DatabaseServer.ProviderName)
                {
                    case "System.Data.Sql":
                    case "System.Data.SQL":
                        dbParameters = new OsqlParameter[] { new OsqlParameter("@objname", ObjectName) };
                        dt = db.QueryDataSet(CommandType.StoredProcedure, ProcedureName, dbParameters).Tables[0];

                        foreach (DataRow dr in dt.Rows)
                            sp.Append("" + dr["Text"] + "");
                        
                        break;
                    case "System.Data.MySql":
                    case "System.Data.MySQL":

                        var d = db.GetDataBaseObject();
                        foreach (DataRow row in d.Rows)
                        {
                            string name = row["table_name"] + "";
                            string type = row["table_type"] + "";
                            if (name.Equals(ObjectName, StringComparison.OrdinalIgnoreCase))
                            {
                                switch (type.ToLower())
                                {
                                    case "view":
                                        SqlTextView = "show create view " + ObjectName + "";
                                        dt = db.QueryDataSet(CommandType.Text, SqlTextView, dbParameters).Tables[0];

                                        foreach (DataRow dr in dt.Rows)
                                            sp.Append("" + dr["Create View"] + "");

                                        break;
                                    case "function":
                                        SqlTextView = "show create function " + ObjectName + "";
                                        dt = db.QueryDataSet(CommandType.Text, SqlTextView, dbParameters).Tables[0];

                                        foreach (DataRow dr in dt.Rows)
                                            sp.Append("" + dr["Create Function"] + "");

                                        break;

                                    case "procedure":
                                        SqlTextView = "show create procedure " + ObjectName + "";
                                        dt = db.QueryDataSet(CommandType.Text, SqlTextView, dbParameters).Tables[0];

                                        foreach (DataRow dr in dt.Rows)
                                            sp.Append("" + dr["Create Procedure"] + "");

                                        break;
                                }

                                break;
                            }
                        }

                       

                        break;

                    case "System.Data.postgreSql":
                    case "System.Data.PostgreSql":

                        string sql = "select prosrc as Text from pg_proc where proname='" + ObjectName + "' ";
                        sql += " union select definition as Text from pg_views where viewname='" + ObjectName + "' ";
                        dt = db.QueryDataSet(CommandType.Text, sql, null).Tables[0];

                        foreach (DataRow dr in dt.Rows)
                            sp.Append("" + dr["Text"] + "");

                        break;
                }       

            } 

            return sp.Value;
        }

        public static string CreateNewProcedure(DataBaseServer DatabaseServer)
        {
            StringPlus sp = new StringPlus();

            if (AppSettings.EditorSettings.KeywordsCase)
            {
                switch (DatabaseServer.ProviderName)
                {
                    case "System.Data.Sql":
                    case "System.Data.SQL":

                        sp.Append("-- =============================================").Append(Environment.NewLine);
                        sp.Append("-- Author:		<Author,,Name>").Append(Environment.NewLine);
                        sp.Append("-- Create date: <Create Date,,>").Append(Environment.NewLine);
                        sp.Append("-- Description:	<Description,,>").Append(Environment.NewLine);
                        sp.Append("-- =============================================").Append(Environment.NewLine);

                        sp.Append("CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>").Append(Environment.NewLine);
                        sp.Append("	<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, ").Append(Environment.NewLine);
                        sp.Append("	<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>").Append(Environment.NewLine);
                        sp.Append("AS").Append(Environment.NewLine);
                        sp.Append("BEGIN").Append(Environment.NewLine);
                        sp.Append("AS").Append(Environment.NewLine);
                        sp.Append("	-- SET NOCOUNT ON added to prevent extra result sets from").Append(Environment.NewLine);
                        sp.Append("	-- interfering with SELECT statements.").Append(Environment.NewLine);
                        sp.Append("	SET NOCOUNT ON;").Append(Environment.NewLine);
                        sp.Append("	-- Insert statements for procedure here").Append(Environment.NewLine);
                        sp.Append("	SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>").Append(Environment.NewLine);
                        sp.Append("END").Append(Environment.NewLine);

                        break;

                    case "System.Data.Sqlite":
                        sp.Append("Sqlite 不支持存储过程").Append(Environment.NewLine);
                        break;
                }
            }
            else
            {
                switch (DatabaseServer.ProviderName)
                {
                    case "System.Data.Sql":
                    case "System.Data.SQL":

                        sp.Append("-- =============================================").Append(Environment.NewLine);
                        sp.Append("-- Author:		<Author,,Name>").Append(Environment.NewLine);
                        sp.Append("-- Create date: <Create Date,,>").Append(Environment.NewLine);
                        sp.Append("-- Description:	<Description,,>").Append(Environment.NewLine);
                        sp.Append("-- =============================================").Append(Environment.NewLine);

                        sp.Append("create procedure <Procedure_Name, sysname, ProcedureName>").Append(Environment.NewLine);
                        sp.Append("	<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, ").Append(Environment.NewLine);
                        sp.Append("	<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>").Append(Environment.NewLine);
                        sp.Append("as").Append(Environment.NewLine);
                        sp.Append("begin").Append(Environment.NewLine);
                        sp.Append("as").Append(Environment.NewLine);
                        sp.Append("	-- set nocount on added to prevent extra result sets from").Append(Environment.NewLine);
                        sp.Append("	-- interfering with select statements.").Append(Environment.NewLine);
                        sp.Append("	set nocount on;").Append(Environment.NewLine);
                        sp.Append("	-- Insert statements for procedure here").Append(Environment.NewLine);
                        sp.Append("	select <@Param1, sysname, @p1>, <@Param2, sysname, @p2>").Append(Environment.NewLine);
                        sp.Append("end").Append(Environment.NewLine);

                        break;

                    case "System.Data.Sqlite":
                        sp.Append("Sqlite 不支持存储过程").Append(Environment.NewLine);
                        break;
                }
            }

            return sp.Value;
        }

        public static string CreateNewView(DataBaseServer DatabaseServer)
        {
            StringPlus sp = new StringPlus();
            if (AppSettings.EditorSettings.KeywordsCase)
            {
                sp.Append("CREATE VIEW <View_Name,sysname,ViewName> ").Append(Environment.NewLine);
                sp.Append(" AS").Append(Environment.NewLine);
            }
            else
            {
                sp.Append("create view <view_name,sysname,viewname> ").Append(Environment.NewLine);
                sp.Append(" as").Append(Environment.NewLine);
            }
            switch (DatabaseServer.ProviderName)
            {
                case "System.Data.Sql":
                case "System.Data.SQL":
                    sp.Append("	--select * from [dbo].[table]").Append(Environment.NewLine);
                    break;
                case "System.Data.Sqlite":
                    sp.Append("	--select * from [table]").Append(Environment.NewLine);
                    break;
                case "System.Data.PostgreSql":
                    sp.Append("	--select * from \"table\"").Append(Environment.NewLine);
                    break;
            }
            if (AppSettings.EditorSettings.KeywordsCase)
            {
                sp.Append("GO").Append(Environment.NewLine);
            }
            else
            {
                sp.Append("go").Append(Environment.NewLine);
            }
            return sp.Value;
        }

        public static string CreateNewFunction(DataBaseServer DatabaseServer, string Type)
        {
            StringPlus sp = new StringPlus();
            return sp.Value;
        }

        public static string CreateTop100Query(string TableName, DataBaseServer DatabaseServer)
        {
            StringPlus sp = new StringPlus();

            if (AppSettings.EditorSettings.KeywordsCase)
            {
                sp.Append("SELECT");

                switch (DatabaseServer.ProviderName)
                {
                    case "System.Data.SQL":
                    case "System.Data.Sql":
                        sp.Append(" TOP 100");
                        break;
                }
            }
            else
            {
                sp.Append("select");

                switch (DatabaseServer.ProviderName)
                {
                    case "System.Data.SQL":
                    case "System.Data.Sql":
                        sp.Append(" top 100");
                        break;
                }
            }

            using (var db = Utility.GetAdohelper(DatabaseServer))
            {
                DataTable dt = db.GetTableObject(TableName);

                int ColumnIndex = 0;
                foreach (DataRow dr in dt.Rows)
                {
                    if (ColumnIndex > 0)
                        sp.Append("\r\n    ," + getObject(dr["ColumnName"], DatabaseServer.ProviderName) + "");
                    else
                        sp.Append(" " + getObject(dr["ColumnName"], DatabaseServer.ProviderName) + "");
                    ColumnIndex++;
                }
            }

            if (AppSettings.EditorSettings.KeywordsCase)
            {
                sp.Append("\r\nFROM ");
            }
            else
            {
                sp.Append("\r\nfrom ");
            }
            sp.Append(getObject(TableName, DatabaseServer.ProviderName) + "");

            if (AppSettings.EditorSettings.KeywordsCase)
            {
                switch (DatabaseServer.ProviderName)
                {
                    case "System.Data.Sqllite":
                    case "System.Data.MySql":
                    case "System.Data.PostgreSql":
                        sp.Append("\r\nLIMIT 100");
                        break;
                }
            }
            else
            {
                switch (DatabaseServer.ProviderName)
                {
                    case "System.Data.Sqllite":
                    case "System.Data.MySql":
                    case "System.Data.PostgreSql":
                        sp.Append("\r\nlimit 100");
                        break;
                }
            }

            return sp.Value;
        }

    }
}
